create or replace package body m4osys_dynsql as

  type v2tab is table of varchar2(4000);

  p_allowed_schemas v2tab;

  -- ===========================================================================
  procedure err(i_msg in varchar2)
  is
  begin
    raise_application_error(
      -20001,
      i_msg);
  end;
  -- ===========================================================================
  procedure create_any_package(i_sql in out nocopy lines_t)
  is
    c           pls_integer;
    v_dummy_res pls_integer;
    v_schema    varchar2(4000);
  begin
    -- preconditions:
    --
    -- - only "create or replace package" allowed
    -- - package must be created with schema prefix
    -- - schema must be registered for use in sys_dynsql_pkg or be the user's
    --
    if not regexp_like(
             i_sql(i_sql.first),
             'create or replace package( body *)? [A-Z_0-9]+\..*') then
      dbms_output.put_line('in "'||i_sql(i_sql.first)||'":');
      err('code does not match "create or replace package <SCHEMA>.*"');
    else
      v_schema := rtrim(regexp_substr(i_sql(i_sql.first),'[A-Z_0-9]+\.'),
                        '.');
      if v_schema member of p_allowed_schemas then
        -- user is allowed to generate code in this schema
        null;
      elsif v_schema = user then
        -- assume user can create package in own schema
        null;
      else
        err('sys_dynsql_pkg is not configured to create code in schema "'||
            v_schema ||
            '" for "'||
            user     ||
            '"');
      end if;
    end if;

    c := dbms_sql.open_cursor;
    begin
      dbms_sql.parse(c,i_sql,i_sql.first,i_sql.last,true,dbms_sql.native);
      v_dummy_res := dbms_sql.execute(c);
      dbms_sql.close_cursor(c);
    exception
    when OTHERS then 
      dbms_output.put_line('ERR='||sqlerrm);
      dbms_sql.close_cursor(c); 
      raise;
    end;
  end;

begin
  dbms_output.put_line('checking m4osys_dynsql_schemas for user '||user);
  select distinct schema_name
  bulk collect into p_allowed_schemas
  from m4osys_dynsql_schemas
  where user_name = user;
end;
/
show err
